
package DBLayer;
import java.sql.*;
import ModelLayer.Invoice;
import java.util.ArrayList;

/**
 * @version 12/22/2011
 * @author Gruppe 4
 */

public class DBInvoice {
    private Connection con;
    private PreparedStatement pStmtSelect;
    
    public DBInvoice(){
        con = DBConnection.getInstance().getDBcon();
    }
    
    public ArrayList<Invoice> getAllInvoices(boolean retrieveAssociation){
        return miscWhere("",retrieveAssociation);
    }
    
    public Invoice findInvoice(int invoiceNo, boolean retrieveAssociation)
    {   
        String wClause = "  where invoiceId = ";
        Invoice invoiceObj = new Invoice();
        invoiceObj = findWithPreQuery(invoiceNo, wClause, retrieveAssociation);
        
        return invoiceObj;
    }
    
    public int insertInvoice(Invoice invoiceObj)
    {
        int rc = -1;
        
        //call to get the next order id
        int nextInvoiceNo = getMaxId();
        nextInvoiceNo = nextInvoiceNo + 1;
        System.out.println("next invoice No = " +  nextInvoiceNo);
        
        PreparedStatement pstmt = null;
        String insert = "insert into Invoice(invoiceNo, paymentDate, amount, invoiceDate)"+
                     "values (?,?,?,?)";
        System.out.println(insert);
        try
        {
            pstmt = con.prepareStatement(insert);
            pstmt.setInt(1,nextInvoiceNo);
            pstmt.setString(2, invoiceObj.getPaymentDate());
            pstmt.setDouble(3, invoiceObj.getAmount());
            pstmt.setString(4, invoiceObj.getInvoiceDate());
            
            rc = pstmt.executeUpdate();
        }//end try
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        
        return rc;
    }
    
    public int updateInvoice(Invoice invoice)
    {
        Invoice invoiceObj  = invoice;
        int rc=-1;
        PreparedStatement pstmt = null;
        
        String query="UPDATE Invoice SET "+
                "invoiceNo = ? "+ 
                "paymentDate = ? "+
                "amount = ? "+
                "invoiceDate = ? "+
               
                "WHERE invoiceNo = ?";
        
        System.out.println("Update query:" + query);
        try{
                    pstmt = con.prepareStatement(query);
                    
                    pstmt.setInt(1, invoiceObj.getInvoiceNo());
                    pstmt.setString(2, invoiceObj.getPaymentDate());
                    pstmt.setDouble(3, invoiceObj.getAmount());
                    pstmt.setString(4, invoiceObj.getInvoiceDate());
                    
                    rc = pstmt.executeUpdate();
	 	 	pstmt.close();
        }
        
        catch(Exception ex){
	 	 System.out.println("Update exception in Invoice db: "+ex);
	  	}
		return(rc);	
        
    }
    
    public int deleteInvoice(int invoiceNo)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "delete from Invoice where invoiceNo = ?";
        System.out.println(delete);
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setInt(1,invoiceNo);
            rc = pstmt.executeUpdate(); 
        }
        catch(SQLException sqlE)
        {
            System.out.println("SQL Error");
            System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        
        return rc;
    }
    
    private ArrayList<Invoice> miscWhere(String wClause, boolean retrieveAssociation){
        ResultSet results;
        ArrayList<Invoice> list = new ArrayList<Invoice>();
        
         String query =  buildQuery(wClause);
         System.out.println("DbInvoice " + query);
         try{
             Statement stmt = con.createStatement();
             stmt.setQueryTimeout(5);
             results = stmt.executeQuery(query);
             
             int snr=0;
		while( results.next() ){
	     	 Invoice invoiceObj = new Invoice();
		 invoiceObj = buildInvoice(results, retrieveAssociation);	
                 list.add(invoiceObj);	
		}//end while
             stmt.close();
         }
    catch(Exception e){
	 		System.out.println("Query exception - select invoice : "+e);
			e.printStackTrace();
	 	}
		return list;
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT * FROM Invoice "; 
            
           

		if (wClause.length()>0)
                query = query + " where " + wClause + "= invoiceNo";
			
		return query;
	}
    
    private Invoice buildInvoice(ResultSet results, boolean retrieveAssociation)
    {
       Invoice invoiceObj = new Invoice();
       
         try{
             if(retrieveAssociation){
               invoiceObj.setInvoiceNo(results.getInt("invoiceNo"));
               invoiceObj.setPaymentDate(results.getString("paymentDate"));
               invoiceObj.setAmount(results.getDouble("Amount"));
               invoiceObj.setInvoiceDate(results.getString("invoiceDate"));
               
          }
         else{ //no Task was found
                            invoiceObj = null;
                        }
         }
         catch(Exception e)
         {
             System.out.println("building invoice object");
         }
        
        return invoiceObj;
        
    }
    
    public Invoice findWithPreQuery(Object value, String wClause, boolean retrieveAssociation)
        {   
            System.out.println("Find with preCompiled");
            int invoiceNo = 0;
            String startDate = "";
            if(pStmtSelect == null)
            {
               pStmtSelect =  getPreparedStmt(wClause);          
            }
            Invoice iObj = null;
            ResultSet results;
            
            try
        {
            if(value instanceof Integer)
            {
                invoiceNo = (Integer) value;
                pStmtSelect.setInt(1,invoiceNo);
            }
            if(value instanceof String)
            {
                startDate = (String) value;
                pStmtSelect.setString(1,startDate);
            }
           
            results = pStmtSelect.executeQuery();
            if(results.next())
            {
                iObj = buildInvoice(results, retrieveAssociation);
            }
        } 
        catch(Exception e)
        {
            e.getMessage();
        }
        
        return iObj;
    }
    
    private PreparedStatement getPreparedStmt(String wClause)
        {
           try{ 
            String query = "Select * FROM Invoice " +
                           wClause + " ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
           }
           catch(Exception e)
           {
                e.getMessage();
            }
           return pStmtSelect;
        }
    
    /**
     * Method to get the highest invoiceNo that exist in the database
     * @return returns the highest invoiceNo
     */
    public int getMaxId()
    {
        int maxInvoiceNo = GetMax.getMaxId("Select max(invoiceNo) from Invoice");
        return maxInvoiceNo;
    }
    
}
